﻿using Newtonsoft.Json;
using OfficeOpenXml;
using PhuLeBussinessObject.Base;
using PhuLeDao.Product;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Web;

namespace PhuLeBooze.Administrator.Views.Product.Class
{
    public class CProductUI
    {
        public string ImportExcel(string filePath, string UserID)
        {
            CApplicationMessage message = new CApplicationMessage();
            string Result = "";
            DataSet dataset = new DataSet();
            CProductDao productDao = new CProductDao();
            try
            {
                productDao.ActionTruncateProductImportTemp("<input />");
                using (var pck = new OfficeOpenXml.ExcelPackage())
                {
                    using (var stream = File.OpenRead(filePath))
                    {
                        pck.Load(stream);
                    }

                    String wSheet = "";

                    foreach (var ws1 in pck.Workbook.Worksheets)
                    {
                        DataTable dt = ReadExcelSheet(ws1);
                        string projectTypeCode = ws1.Name;
                        try
                        {
                            foreach (DataRow r in dt.Rows)
                            {
                                string releaseDate = "1900-01-01";
                                if (r[3].ToString() != "")
                                {
                                    string[] arrString = (r[3].ToString()).Split(new Char[] { '/'});

                                    if (arrString.Length==3)
                                        releaseDate = string.Format("{0}-{1}-{2}", arrString[2], arrString[1], arrString[0]);
                                }
                                string partern = "<InputValue SessionKey=\"{0}\" Code=\"{1}\" ProductTypeName=\"{2}\" Description=\"{3}\" ReleaseDate=\"{4}\"   Action=\"Insert\"/>";
                                if (r[0].ToString() != "")
                                    productDao.ActionImportProduct(string.Format(partern, UserID, HttpUtility.HtmlEncode(r[0].ToString()), projectTypeCode, HttpUtility.HtmlEncode(r[2].ToString()), releaseDate));
                                if ("AC/13P 2780941" == r[0].ToString())
                                    releaseDate = "1900-01-01";
                            }
                        }
                        catch (Exception ex2)
                        {

                        }
                        dataset.Tables.Add(dt);
                    }


                }
            }
            catch (Exception ex)
            {

            }
            message.IsSuccessfull = true;
            return JsonConvert.SerializeObject(message);

        }
        private DataTable ReadExcelSheet(ExcelWorksheet sheet)
        {
            try
            {
                DataTable tbl = new DataTable(sheet.Name);
                bool hasHeader = true; // adjust it accordingly( i've mentioned that this is a simple approach)
                foreach (var firstRowCell in sheet.Cells[1, 1, 1, sheet.Dimension.End.Column])
                {
                    tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
                }
                var startRow = hasHeader ? 2 : 1;
                for (var rowNum = startRow; rowNum <= sheet.Dimension.End.Row; rowNum++)
                {
                    var wsRow = sheet.Cells[rowNum, 1, rowNum, sheet.Dimension.End.Column];
                    var row = tbl.NewRow();
                    foreach (var cell in wsRow)
                    {
                        if (cell.Start.Column - 1 < tbl.Columns.Count)
                            row[cell.Start.Column - 1] = cell.Text;
                    }
                    tbl.Rows.Add(row);
                }
                return tbl;
            }
            catch (Exception e)
            {
                throw new Exception("Can not read excel sheet: " + sheet.Name);
            }
        }

    }
}